<%@ page language="java" pageEncoding="utf-8"%>
<%@ page import="java.sql.*,java.util.*,java.text.SimpleDateFormat,java.lang.Math" %>
<%@ page isELIgnored="false" %> 
<%@ include file="classes/conndb.jsp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
    <head>
        <style>
            body{margin:0px; padding:0px; font-size:12px; font-family:"SimSun"; color:#000; }
            a{color:#000; text-decoration:none;}
            a:hover{ text-decoration:none; color:#F00;}
        </style>
    </head>

    <body>
        <%
            request.setCharacterEncoding("UTF-8");
            //session.setAttribute("loginId", "zhuof");//信息中心
            //session.setAttribute("loginId", "nwhzs");//县农业委员会
            //session.setAttribute("loginId", "wangji");//开普互联
            String loginId = (String) (session.getAttribute("loginId") == null ? "" : session.getAttribute("loginId"));//用户ID
            String keyword1 = request.getParameter("keyword1") == null ? "" : request.getParameter("keyword1");
        %>
        <%  Connection conn = null;
            PreparedStatement pst = null;
            ResultSet rs = null, rs2 = null,rs3 = null, rss = null;

            String url = "";
            String sql = "";
            String number = "";
            String depsql = "";
            String depsql2 = "";
            String depsql3 = "";
            String userid = "";
            String username = "";
            String deptname = "";
            String wheresql = "";

            int pageSize = 10;
            int currentPage = 1;
            int total = 0;


            if ((request.getParameter("currentPage")) != null) {
                currentPage = Integer.valueOf(request.getParameter("currentPage"));
            }

            if (pageSize == 0) {
                pageSize = 10;
            }
            if (currentPage <= 0) {
                currentPage = 1;
            }

            int start = (currentPage - 1) * pageSize;
            int end = (currentPage - 1) * pageSize + pageSize;

            try {
                //接收id与操作选项
                String id1 = request.getParameter("hidden") == null ? "" : request.getParameter("hidden");
                String id2 = request.getParameter("hidden2") == null ? "" : request.getParameter("hidden2");
                String choice = request.getParameter("hidden3") == null ? "" : request.getParameter("hidden3");


                conn = getConnection();
                depsql = "select login_id,user_id,a.name username,b.name deptname from cms_user a join cms_department  b on a.dept_id=b.dept_id where  a.login_id='" + loginId + "'";
                pst = conn.prepareStatement(depsql);
                rss = pst.executeQuery();
                while (rss.next()) {
                    username = rss.getString("username");
                    deptname = rss.getString("deptname");
                    userid = rss.getString("user_id");
                }
                if (!deptname.equals("开普互联") && !deptname.equals("信息中心")) {
                    wheresql = " and creator_id='" + userid + "'";
                }
                if (!keyword1.equals("")) {
                    wheresql += " and title like '%" + keyword1 + "%'";
                }
                //删除
                if (choice.equals("1") && !id1.equals("") && !id2.equals("")) {
                    sql="delete from nrbz_cy where rowid in (select max(rowid) from nrbz_cy where mid='" + id1 + "' and chid='" + id2 + "') and mid='" + id1 + "' and chid='" + id2 + "'";
                    pst = conn.prepareStatement(sql);
                    pst.executeUpdate();
                }
                //列表显示
                sql = "select * from nrbz_cy a join cms_manuscript b on a.mid =b.manuscript_id where (a.mid <> 'null' or a.chid <> 'null' or a.un <> 'null') " + wheresql + " order by b.published_time desc";
                //out.print(sql);
                String countSql = "select count(*) from (" + sql + ")";
                pst = conn.prepareStatement(countSql);
                rs2 = pst.executeQuery();
                while (rs2.next()) {
                    total = rs2.getInt(1);
                }
                String sql1 = "select tt3.* from (select rownum as r,tt2.* from (";
                String sql2 = ") tt2 where rownum<= ?) tt3 where tt3.r > ? ";
                String querySql = sql1 + sql + sql2;
                pst = conn.prepareStatement(querySql);
                pst.setObject(1, end);
                pst.setObject(2, start);
                rs = pst.executeQuery();

                Map pageMap = new HashMap();
                pageMap.put("currentPage", currentPage);
                pageMap.put("total", total);
                request.setAttribute("page", pageMap);
        %>
        <form name="myform" action="" method="post" id="myform">
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
                <tr>
                    <td colspan="4" align="center">关键字(标题)：<input type="text" name="keyword1"/><input type="submit" value="查询" /></td>
                </tr>
                <tr>
                    <td width="55%" height="40" style="text-indent:20px; border-bottom:1px dotted #CCC;text-align:center;">标题</td>
                    <td width="10%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">栏目</td>
                    <td width="10%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">时间</td>
                    <td width="15%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">部门</td>
                    <td width="10%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">操作</td>
                </tr>
                <%
                    while (rs.next()) {
                        String title = rs.getString("TITLE");
                        String n = rs.getString("PUBLISHED_TIME");
                        String mid = rs.getString("MID");
                        String chid = rs.getString("CHID");
                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                        String dateString = formatter.format(new java.util.Date(Long.parseLong(n)));
                        userid = rs.getString("creator_id");
                        String manuscript_id = rs.getString("manuscript_id");
                        String channel_id = rs.getString("channel_id");
                        url = "http://htgl.cnsn.gov.cn/website-webapp/wcm/initEdit_manuscript.action?websiteId=&manuscriptId=" + manuscript_id + "&status=4&channelId=" + channel_id;
                %>
                <tr>
                    <td width="55%" height="40" style="text-indent:20px; border-bottom:1px dotted #CCC;text-align:left;"><a href="<%=url%>" target="_blank"><%=title%></a></td>
                    <td width="10%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">
                        <%
                            depsql3 = "select * from cms_channel  where channel_id ='" + chid + "'";
                            pst = conn.prepareStatement(depsql3);
                            rs3 = pst.executeQuery();
                            while (rs3.next()) {
                                out.print(rs3.getString("channel_name"));
                            }
                        %>
                    </td>
                    <td width="10%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">[<%=dateString%>]</td>
                    <td width="15%" style="color:#666; border-bottom:1px dotted #CCC; text-align:center;">
                        <%
                            depsql2 = "select * from cms_user a join cms_department b on a.dept_id=b.dept_id where a.user_id ='" + userid + "'";
                            pst = conn.prepareStatement(depsql2);
                            rs2 = pst.executeQuery();
                            while (rs2.next()) {
                                out.print(rs2.getString("name"));
                            }
                        %>
                    </td>
                    <td width="10%" style="color:#666; border-bottom:1px dotted #CCC;text-align:center;"><input type='button' name='delete' value='删除' onclick="doit('<%=mid%>','<%=chid%>',1);"/></td>
                </tr>
                <%
                        }
                        closeConn(conn, pst, rs);
                    } catch (SQLException e1) {
                        out.print("" + e1);
                    }
                %>
                <tr align="center" height="30">
                    <td colspan="4">
                        <jsp:include page="common/page.jsp" >
                            <jsp:param value="${page.currentPage }" name="currentPage"/>
                            <jsp:param value="${page.total }" name="totalRecord"/>
                            <jsp:param value="10" name="pageSize"/>
                            <jsp:param value='myform' name="formName"/>
                        </jsp:include>

                    </td>
                </tr>
            </table>
            <INPUT type="hidden" name="hidden" id="hidden"/>
            <INPUT type="hidden" name="hidden2" id="hidden2"/>
            <INPUT type="hidden" name="hidden3" id="hidden3"/>
        </form>

        <script language=javascript>
            function doit(k,m,j)
            {
                if(confirm("数据删除后不能恢复，您确定要删除？")){
                    document.getElementById("hidden").value=k;
                    document.getElementById("hidden2").value=m;
                    document.getElementById("hidden3").value=j;
                    document.getElementById("myform").submit();
                }
            }
        </script>

    </body>
</html>